<?php
namespace Swork\Db;

use Swork\Client\MySql;
use Swork\Exception\DbException;

class MySqlJoin extends MySql
{
    /**
     * 左边表名
     * @var string
     */
    private $leftTbl;

    /**
     * 左边对象
     * @var MySqlModel
     */
    private $leftModel;

    /**
     * 已经连接的JOIN条件
     * @var array
     */
    private $joins;

    /**
     * 子查询配置
     * @var array
     */
    private $selectAS;

    /**
     * 联合表简写符号
     * @var array
     */
    private static $shorts = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'];

    /**
     * MySqlJoin constructor.
     * @param MySqlModel $leftModel 左边对象
     * @param MySqlModel $rightModel 右边对象
     * @param array $on 联合表条件
     * @param array $as 联合子表参数（默认为空）
     * @param array $joins 已经连接的表
     */
    public function __construct(MySqlModel $leftModel, MySqlModel $rightModel, array $on, array $as = [], array $joins = [])
    {
        $this->leftTbl = $leftModel->getTbl();
        $this->leftModel = $leftModel;
        $this->joins = $joins;
        $this->joins[] = [$leftModel, $rightModel, $as, $on];
    }

    /**
     * 联合表查询
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @return MySqlJoin
     */
    function join(MySqlModel $model, array $on): MySqlJoin
    {
        $on['_'] = 'INNER';
        return new MySqlJoin($this->leftModel, $model, $on, [], $this->joins);
    }

    /**
     * 联合表查询（左联）
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @return MySqlJoin
     */
    function leftJoin(MySqlModel $model, array $on): MySqlJoin
    {
        $on['_'] = 'LEFT';
        return new MySqlJoin($this->leftModel, $model, $on, [], $this->joins);
    }

    /**
     * 联合表查询（右联）
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @return MySqlJoin
     */
    function rightJoin(MySqlModel $model, array $on): MySqlJoin
    {
        $on['_'] = 'RIGHT';
        return new MySqlJoin($this->leftModel, $model, $on, [], $this->joins);
    }

    /**
     * 联合表强查询
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @return MySqlJoin
     */
    function straightJoin(MySqlModel $model, array $on): MySqlJoin
    {
        $on['_'] = 'STRAIGHT';
        return new MySqlJoin($this->leftModel, $model, $on, [], $this->joins);
    }

    /**
     * 联合子表查询
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序，['otime' => 1]
     * @param int $size 输出数量，0表示全部
     * @param int $idx 页码位置，从1开始，0表示不使用翻页
     * @return MySqlJoin
     */
    function joinAS(MySqlModel $model, array $on, array $where = [], string $cols = '*', array $order = [], int $size = 0, int $idx = 0): MySqlJoin
    {
        $on['_'] = 'INNER';
        $as = [
            'where' => $where,
            'cols' => $cols,
            'order' => $order,
            'size' => $size,
            'idx' => $idx
        ];
        return new MySqlJoin($this->leftModel, $model, $on, $as, $this->joins);
    }

    /**
     * 联合子表查询（左联）
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序，['otime' => 1]
     * @param int $size 输出数量，0表示全部
     * @param int $idx 页码位置，从1开始，0表示不使用翻页
     * @return MySqlJoin
     */
    function leftJoinAS(MySqlModel $model, array $on, array $where = [], string $cols = '*', array $order = [], int $size = 0, int $idx = 0): MySqlJoin
    {
        $on['_'] = 'LEFT';
        $as = [
            'where' => $where,
            'cols' => $cols,
            'order' => $order,
            'size' => $size,
            'idx' => $idx
        ];
        return new MySqlJoin($this->leftModel, $model, $on, $as, $this->joins);
    }

    /**
     * 联合子表查询（右联）
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序，['otime' => 1]
     * @param int $size 输出数量，0表示全部
     * @param int $idx 页码位置，从1开始，0表示不使用翻页
     * @return MySqlJoin
     */
    function rightJoinAS(MySqlModel $model, array $on, array $where = [], string $cols = '*', array $order = [], int $size = 0, int $idx = 0): MySqlJoin
    {
        $on['_'] = 'RIGHT';
        $as = [
            'where' => $where,
            'cols' => $cols,
            'order' => $order,
            'size' => $size,
            'idx' => $idx
        ];
        return new MySqlJoin($this->leftModel, $model, $on, $as, $this->joins);
    }

    /**
     * 联合表子查询（强联）
     * @param MySqlModel $model 右表对象
     * @param array $on 联合条件，['左表字段' => '右表字段']
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序，['otime' => 1]
     * @param int $size 输出数量，0表示全部
     * @param int $idx 页码位置，从1开始，0表示不使用翻页
     * @return MySqlJoin
     */
    function straightJoinAS(MySqlModel $model, array $on, array $where = [], string $cols = '*', array $order = [], int $size = 0, int $idx = 0): MySqlJoin
    {
        $on['_'] = 'STRAIGHT';
        $as = [
            'where' => $where,
            'cols' => $cols,
            'order' => $order,
            'size' => $size,
            'idx' => $idx
        ];
        return new MySqlJoin($this->leftModel, $model, $on, $as, $this->joins);
    }

    /**
     * 最外层的子查询（在getList\getRow\getDistinct之前调用）
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序，['otime' => 1]
     * @param int $size 输出数量，0表示全部
     * @return MySqlJoin
     */
    function selectAS(array $where = [], string $cols = '*', array $order = [], int $size = 0): MySqlJoin
    {
        $this->selectAS = [
            'where' => $where,
            'cols' => $cols,
            'order' => $order,
            'size' => $size,
        ];
        return $this;
    }

    /**
     * 获取列表数据
     * @param array $where 数据条件（按顺序A表，B表联合，默认A表） ['A.id' => 123, 'B.name' => 'xixi', '$or' => [ id=>5, age => 9]]
     * @param string $cols 输出的字段，使用逗号分隔
     * @param array $order 排序 ['odr' => 1, 'atime' => -1]
     * @param int $size 输出数量，0表示全部
     * @param int $idx 页码位置，从1开始，0表示不使用翻页
     * @return array
     * @throws
     */
    function getList(array $where = [], string $cols = '*', array $order = [], $size = 0, int $idx = 0)
    {
        //联合表字段
        $columns = [];
        $items = $this->getJoinTableColumns($columns);

        //分析数据条件
        $query = new MySqlQuery($this->leftTbl, $items, $where, $order);
        $condition = $query->getCondition();
        $orderby = $query->getOrderBy();
        $indexs = $condition['index'];

        //合并字段
        if (empty($cols) || $cols == '*')
        {
            $cols = join(',', array_keys($items));
        }

        //合成SQL
        $sql = "SELECT $cols FROM `{$this->leftModel->getTbl()}` A";
        if (is_array($indexs) && isset($indexs['A']))
        {
            $sql .= $indexs['A'];
        }
        $sql .= $this->getJoinOnCondition($indexs, $condition);
        $sql .= $condition['where'];

        //排序
        if (!empty($orderby))
        {
            $sql .= " ORDER BY $orderby";
        }

        //数量
        if ($size > 0)
        {
            if ($idx > 0)
            {
                $offset = $size * ($idx - 1);
                $sql .= " LIMIT $offset,$size";
            }
            else
            {
                $sql .= " LIMIT $size";
            }
        }

        //外层包子查询
        if ($this->selectAS != null)
        {
            //输入的参数
            $where = $this->selectAS['where'];
            $order = $this->selectAS['order'];
            $cols = $this->selectAS['cols'];
            $size = $this->selectAS['size'];

            //默认字段
            if (empty($cols))
            {
                $cols = '*';
            }

            //重组items（去掉表别名前缀）
            $tmps = [];
            foreach ($items as $key => $item)
            {
                if (strpos($key, '.') == 1)
                {
                    $key = substr($key, 2);
                }
                $tmps[$key] = $item;
            }

            //分析数据条件
            $queryAS = new MySqlQuery($this->leftTbl . '_AS', $tmps, $where, $order);
            $conditionAS = $queryAS->getCondition();
            $orderbyAS = $queryAS->getOrderBy();

            //合成SQL
            $sql = "SELECT $cols FROM ($sql) AS T";
            $sql .= $conditionAS['where'];

            //排序
            if (!empty($orderbyAS))
            {
                $sql .= " ORDER BY $orderbyAS";
            }

            //数量
            if ($size > 0)
            {
                $sql .= " LIMIT $size";
            }

            //累计types和values
            if (count($conditionAS['types']) > 0)
            {
                $condition['types'] = array_merge($condition['types'], $conditionAS['types']);
            }
            if (count($conditionAS['values']) > 0)
            {
                $condition['values'] = array_merge($condition['values'], $conditionAS['values']);
            }
        }

        //结束符
        $sql .= ';';

        //执行
        $this->getCollector($this->leftModel->getNode());
        $result = $this->execute($sql, $condition['types'], $condition['values'], true);

        //获取结果，并转化为相应字段类型
        $list = $result['Results'];
        if (count($condition['types']) == 0)
        {
            MySqlUtility::forceToDataType($columns, $list);
        }

        //返回结果
        return $list ?: [];
    }

    /**
     * 合计数量
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 输出的字段，如 A.qty,B.amt
     * @return array
     * @throws
     */
    function getSum(array $where, string $cols)
    {
        //安全判断
        if (empty($cols) || $cols == '')
        {
            throw new DbException('cols can\'t be empty!');
        }

        //拆成多个
        $tmp = [];
        $cols = explode(',', $cols);
        foreach ($cols as $col)
        {
            $tmp[] = 'SUM(' . $col . ') as ' . preg_replace('/^[A-B]\./', '', $col);
        }

        //获取数据并返回
        return $this->getList($where, join(',', $tmp));
    }

    /**
     * 通过条件获取一行数据
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id=>5, age => 9]]
     * @param string $cols 输出的字段，使用逗号分隔
     * @param array $order 排序 ['odr' => 1, 'atime' => -1]
     * @return bool|array
     * @throws
     */
    public function getRow(array $where = [], string $cols = '*', array $order = [])
    {
        $result = $this->getList($where, $cols, $order, 1);
        if (count($result) > 0)
        {
            return $result[0];
        }
        return false;
    }

    /**
     * 获取数量
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 输出的字段，默认是*，或 distinct(uid)，只能一个字段
     * @return int
     * @throws
     */
    function getCount(array $where = [], string $cols = '*')
    {
        //联合表字段
        $items = $this->getJoinTableColumns();

        //分析数据条件
        $query = new MySqlQuery($this->leftTbl, $items, $where, []);
        $condition = $query->getCondition();
        $indexs = $condition['index'];

        //合成SQL
        $cols = ($this->selectAS == null) ? "COUNT($cols)" : $cols;
        $sql = "SELECT $cols FROM `{$this->leftModel->getTbl()}` A";
        if (is_array($indexs) && isset($indexs['A']))
        {
            $sql .= $indexs['A'];
        }
        $sql .= $this->getJoinOnCondition($indexs, $condition);
        $sql .= $condition['where'];

        //外层包子查询
        if ($this->selectAS != null)
        {
            //输入的参数
            $where = $this->selectAS['where'];
            $order = $this->selectAS['order'];
            $cols = $this->selectAS['cols'];

            //重组items（去掉表别名前缀）
            $tmps = [];
            foreach ($items as $key => $item)
            {
                if (strpos($key, '.') == 1)
                {
                    $key = substr($key, 2);
                }
                $tmps[$key] = $item;
            }

            //分析数据条件
            $queryAS = new MySqlQuery($this->leftTbl . '_AS', $tmps, $where, $order);
            $conditionAS = $queryAS->getCondition();

            //合成SQL
            $sql = "SELECT COUNT($cols) FROM ($sql) AS T";
            $sql .= $conditionAS['where'];

            //累计types和values
            if (count($conditionAS['types']) > 0)
            {
                $condition['types'] = array_merge($condition['types'], $conditionAS['types']);
            }
            if (count($conditionAS['values']) > 0)
            {
                $condition['values'] = array_merge($condition['values'], $conditionAS['values']);
            }
        }

        //结束符
        $sql .= ';';

        //执行
        $this->getCollector($this->leftModel->getNode());
        $result = $this->execute($sql, $condition['types'], $condition['values'], true);

        //返回结果
        if ($result != false && count($result['Results']) > 0)
        {
            return (int)array_values($result['Results'][0])[0];
        }
        return 0;
    }

    /**
     * 获取数据字典对象 key-info 格式（仅能用于少量数据的情况，不推荐在大量数据是使用）
     * @param string $key 字典的KEY
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序 ['odr' => 1, 'atime' => -1]
     * @return array
     * @throws
     */
    function getDict(string $key, array $where = [], $cols = '*', array $order = [])
    {
        //补充where语句和字段
        if (trim($cols) != '*' && strpos($cols, $key) === false)
        {
            $cols .= ',' . $key;
        }

        //获取数据
        $list = $this->getList($where, $cols, $order);
        if ($list == false)
        {
            return [];
        }

        //返回
        return array_column($list, null, $key);
    }

    /**
     * 获取数据字典列表 key-list 格式（仅能用于少量数据的情况，不推荐在大量数据是使用）
     * @param string $key 字典的KEY
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @param string $cols 需要输出的字段，逗号分隔
     * @param array $order 排序 ['odr' => 1, 'atime' => -1]
     * @return array
     * @throws
     */
    function getDicts(string $key, array $where = [], $cols = '*', array $order = [])
    {
        //补充where语句和字段
        if (trim($cols) != '*' && strpos($cols, $key) === false)
        {
            $cols .= ',' . $key;
        }

        //获取数据
        $list = $this->getList($where, $cols, $order);
        if ($list == false)
        {
            return [];
        }

        //转成字典
        $dict = [];
        foreach ($list as $value)
        {
            $dict[$value[$key]][] = $value;
        }

        //返回
        return $dict;
    }

    /**
     * 获取某个字段列表（返回一维数组）
     * @param string $col 要输出的字段（只能是一个字段）
     * @param array $where 数据条件 ['id' => 123, 'name' => 'xixi', '$or' => [ id => 5, age => 9]]
     * @return array
     */
    function getDistinct(string $col, array $where = [])
    {
        //获取数据
        $list = $this->getList($where, "DISTINCT($col) as col");
        if ($list == false)
        {
            return [];
        }

        //返回
        return array_column($list, 'col');
    }

    /**
     * 通过条件判断是否存在
     * @param array $where 数据条件
     * @return bool
     * @throws
     */
    function exist($where)
    {
        $result = $this->getList($where, '1', [], 1);
        if (count($result) > 0)
        {
            return true;
        }
        return false;
    }

    /**
     * 获取联合表字段列表
     * @param array $cols 真实输出的字段
     * @return array
     */
    private function getJoinTableColumns(array &$cols = [])
    {
        //联合表字段
        $list = [];

        //合并最左边表的字段
        foreach ($this->leftModel->getCols() as $name => $item)
        {
            $list[self::$shorts[0] . '.' . $name] = $item;
            $cols[$name] = $item;
        }

        //合并其它联合表的字段
        foreach ($this->joins as $idx => $join)
        {
            foreach ($join[1]->getCols() as $name => $item)
            {
                $list[self::$shorts[$idx + 1] . '.' . $name] = $item;
                $cols[$name] = $item;
            }
        }
        return $list;
    }

    /**
     * 获取联合条件SQL部分
     * @param mixed $indexs 强制索引
     * @param array $mainCondition 主查询条件对象
     * @return string
     */
    private function getJoinOnCondition($indexs, array &$mainCondition)
    {
        $list = [];
        $types = [];
        $values = [];
        $isIndexs = is_array($indexs);
        foreach ($this->joins as $num => $join)
        {
            $index = '';
            $tbl = $join[1]->getTbl();

            //关联子表部分
            if (count($join[2]) == 0)
            {
                $tbl = "`$tbl`";
            }

            //关联子查询部分
            else
            {
                $items = $join[1]->getCols();
                $query = new MySqlQuery($tbl, $items, $join[2]['where'], $join[2]['order']);
                $condition = $query->getCondition();
                $indexs = $condition['index'];

                //合并字段
                $cols = $join[2]['cols'];
                if (empty($cols) || $cols == '*')
                {
                    $cols = '`' . join('`,`', array_keys($items)) . '`';
                }

                //页码
                $size = $join[2]['size'];
                $idx = $join[2]['idx'];
                $orderby = $query->getOrderBy();

                //合成子SQL
                $sql = "(SELECT $cols FROM `$tbl`";
                $sql .= $condition['where'];

                //排序
                if (!empty($orderby))
                {
                    $sql .= " ORDER BY $orderby";
                }

                if ($size > 0)
                {
                    if ($idx > 0)
                    {
                        $offset = $size * ($idx - 1);
                        $sql .= " LIMIT $offset,$size";
                    }
                    else
                    {
                        $sql .= " LIMIT $size";
                    }
                }
                $sql .= ')';
                $tbl = $sql;

                //累计参数化数据
                $types = array_merge($types, $condition['types']);
                $values = array_merge($values, $condition['values']);
            }

            //所有ON关联关系
            $onKeys = array_keys($join[3]);

            //------------- 处理On关系的第一个条件 -------------
            $left = $onKeys[0];
            $right = $join[3][$left];

            //左边连接前缀
            $short1 = 'A';
            if (strpos($left, '.') == 1)
            {
                $short1 = substr($left, 0, 1);
                $left = substr($left, 2);
            }

            //右边连接前缀
            $short2 = self::$shorts[$num + 1];
            if (strpos($right, '.') == 1)
            {
                $short2 = substr($right, 0, 1);
                $right = substr($right, 2);
            }

            //join连接符
            $conn = $join[3]['_'] ?? '';
            if ($conn == 'STRAIGHT')
            {
                $conn = 'STRAIGHT_JOIN';
            }
            else
            {
                $conn = "$conn JOIN";
            }

            //连接表的索引
            if ($isIndexs && isset($indexs[$short2]))
            {
                $index = $indexs[$short2];
            }

            //累计ON的第一个数据条件
            $list[] = " $conn $tbl $short2$index ON $short1.`{$left}`=$short2.`{$right}`";

            //------------- 处理On关系的其它条件 -------------
            for ($m = 1, $n = count($onKeys); $m < $n; $m++)
            {
                //拿到关联条件的左边字段和右边字段
                $left = $onKeys[$m];
                $right = $join[3][$left];
                if ($left == '_')
                {
                    continue;
                }

                //左边连接前缀
                $short1 = 'A';
                if (strpos($left, '.') == 1)
                {
                    $short1 = substr($left, 0, 1);
                    $left = substr($left, 2);
                }

                //右边连接前缀
                $short2 = self::$shorts[$num + 1];
                if (strpos($right, '.') == 1)
                {
                    $short2 = substr($right, 0, 1);
                    $right = substr($right, 2);
                }

                //累计ON的其它数据条件
                $list[] = " AND $short1.`{$left}`=$short2.`{$right}`";
            }
        }

        //累计参数化数据
        $mainCondition['types'] = array_merge($types, $mainCondition['types']);
        $mainCondition['values'] = array_merge($values, $mainCondition['values']);

        //返回
        return join('', $list);
    }
}
